﻿CREATE PROCEDURE SP_GET_TABLE_INFO @ObjName VARCHAR(128) /* The table to generate sql script */
AS 
    DECLARE @Script VARCHAR(255)
    DECLARE @ColName VARCHAR(30)
    DECLARE @ColID TINYINT
    DECLARE @UserType SMALLINT
    DECLARE @TypeName SYSNAME
    DECLARE @Length TINYINT
    DECLARE @Prec TINYINT
    DECLARE @Scale TINYINT
    DECLARE @Status TINYINT
    DECLARE @cDefault INT
    DECLARE @DefaultID TINYINT
    DECLARE @Const_Key VARCHAR(255)
    DECLARE @IndID SMALLINT 
    DECLARE @IndStatus INT
    DECLARE @Index_Key VARCHAR(255)
    DECLARE @DBName VARCHAR(30)
    DECLARE @strPri_Key VARCHAR(255)
 
/*
** Check to see the the table exists and initialize @objid.
*/
    IF NOT EXISTS ( SELECT  name
                    FROM    sysobjects
                    WHERE   name = @ObjName ) 
        BEGIN
            SELECT  @DBName = DB_NAME()
            RAISERROR(15009,-1,-1,@ObjName,@DBName)
            RETURN (1)
        END
    CREATE TABLE #spscript
        (
          id INT IDENTITY
                 NOT NULL ,
          Script VARCHAR(255) NOT NULL ,
          LastLine TINYINT
        )
    DECLARE Cursor_Column INSENSITIVE CURSOR
    FOR
        SELECT  a.name ,
                a.ColID ,
                a.usertype ,
                b.name ,
                a.length ,
                a.prec ,
                a.scale ,
                a.Status ,
                a.cDefault ,
                CASE a.cdefault
                  WHEN 0 THEN ' '
                  ELSE ( SELECT c.Text
                         FROM   syscomments c
                         WHERE  a.cdefault = c.id
                       )
                END const_key
        FROM    syscolumns a ,
                systypes b
        WHERE   OBJECT_NAME(a.id) = @ObjName
                AND a.usertype = b.usertype
        ORDER BY a.ColID
    SET nocount ON
    SELECT  @Script = 'Create table ' + @ObjName + '('
    INSERT  INTO #spscript
    VALUES  ( @Script, 0 )
/* Get column information */
    OPEN Cursor_Column
    FETCH NEXT FROM Cursor_Column INTO @ColName, @ColID, @UserType, @TypeName,
        @Length, @Prec, @Scale, @Status, @cDefault, @Const_Key
    SELECT  @Script = '' 
    WHILE ( @@FETCH_STATUS <> -1 ) 
        BEGIN
            IF ( @@FETCH_STATUS <> -2 ) 
                BEGIN
                    SELECT  @Script = @ColName + ' ' + @TypeName
                    IF @UserType IN ( 1, 2, 3, 4 ) 
                        SELECT  @Script = @Script + '('
                                + CONVERT(CHAR(3), @Length) + ') '
                    ELSE 
                        IF @UserType IN ( 24 ) 
                            SELECT  @Script = @Script + '('
                                    + CONVERT(CHAR(3), @Prec) + ','
                                    + CONVERT(CHAR(3), @Scale) + ') '
                        ELSE 
                            SELECT  @Script = @Script + ' '
                    IF ( @Status & 0x80 ) > 0 
                        SELECT  @Script = @Script + ' IDENTITY(1,1) '
                    IF ( @Status & 0x08 ) > 0 
                        SELECT  @Script = @Script + ' NULL '
                    ELSE 
                        SELECT  @Script = @Script + ' NOT NULL '
                    IF @cDefault > 0 
                        SELECT  @Script = @Script + ' DEFAULT ' + @Const_Key
                END
            FETCH NEXT FROM Cursor_Column INTO @ColName, @ColID, @UserType,
                @TypeName, @Length, @Prec, @Scale, @Status, @cDefault,
                @Const_Key
            IF @@FETCH_STATUS = 0 
                BEGIN
                    SELECT  @Script = @Script + ',' 
                    INSERT  INTO #spscript
                    VALUES  ( @Script, 0 )
                END
            ELSE 
                BEGIN
                    INSERT  INTO #spscript
                    VALUES  ( @Script, 1 )
                    INSERT  INTO #spscript
                    VALUES  ( ')', 0 )
                END
        END
    CLOSE Cursor_Column
    DEALLOCATE Cursor_Column
/* Get index information */
    DECLARE Cursor_Index INSENSITIVE CURSOR
    FOR
        SELECT  name ,
                IndID ,
                status
        FROM    sysindexes
        WHERE   OBJECT_NAME(id) = @ObjName
                AND IndID > 0
                AND IndID <> 255
        ORDER BY IndID /*增加了对InDid为255的判断*/
    OPEN Cursor_Index
    FETCH NEXT FROM Cursor_Index INTO @ColName, @IndID, @IndStatus
    WHILE ( @@FETCH_STATUS <> -1 ) 
        BEGIN
            IF @@FETCH_STATUS <> -2 
                BEGIN
                    DECLARE @i TINYINT
                    DECLARE @thiskey VARCHAR(50)
                    DECLARE @IndDesc VARCHAR(68) /* string to build up index desc in */
                    SELECT  @i = 1
                    WHILE ( @i <= 16 ) 
                        BEGIN
                            SELECT  @thiskey = INDEX_COL(@ObjName, @IndID, @i)
                            IF @thiskey IS NULL 
                                BREAK
                            IF @i = 1 
                                SELECT  @Index_Key = INDEX_COL(@ObjName,
                                                              @IndID, @i)
                            ELSE 
                                SELECT  @Index_Key = @Index_Key + ', '
                                        + INDEX_COL(@ObjName, @IndID, @i)
                            SELECT  @i = @i + 1
                        END
                    IF ( @IndStatus & 0x02 ) > 0 
                        SELECT  @Script = 'Create unique '
                    ELSE 
                        SELECT  @Script = 'Create '
                    IF @IndID = 1 
                        SELECT  @Script = @Script + ' clustered '

                    IF ( @IndStatus & 0x800 ) > 0 
                        SELECT  @strPri_Key = ' PRIMARY KEY (' + @Index_Key
                                + ')'
                    ELSE 
                        SELECT  @strPri_Key = ''
                    IF @IndID > 1 
                        SELECT  @Script = @Script + ' nonclustered '
                    SELECT  @Script = @Script + ' index ' + @ColName + ' ON '
                            + @ObjName + '(' + @Index_Key + ')'
                    SELECT  @IndDesc = '' 
/*
** See if the index is ignore_dupkey (0x01).
*/
                    IF @IndStatus & 0x01 = 0x01 
                        SELECT  @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
/*
** See if the index is ignore_dup_row (0x04).
*/
/* if @IndStatus & 0x04 = 0x04 */
/* Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ',' */ /* 2000 不在支持*/ 
/*
** See if the index is allow_dup_row (0x40).
*/
                    IF @IndStatus & 0x40 = 0x40 
                        SELECT  @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
                    IF @IndDesc <> '' 
                        BEGIN
                            SELECT  @IndDesc = SUBSTRING(@IndDesc, 1,
                                                         DATALENGTH(@IndDesc)
                                                         - 1)
                            SELECT  @Script = @Script + ' WITH ' + @IndDesc
                        END
/*
** Add the location of the data.
*/
                END
            IF ( @strPri_Key = '' ) 
                INSERT  INTO #spscript
                VALUES  ( @Script, 0 )
            ELSE 
                UPDATE  #spscript
                SET     Script = Script + @strPri_Key
                WHERE   LastLine = 1
            FETCH NEXT FROM Cursor_Index INTO @ColName, @IndID, @IndStatus
        END

    CLOSE Cursor_Index
    DEALLOCATE Cursor_Index
    SELECT  Script
    FROM    #spscript
    SET nocount OFF
    RETURN (0)